/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package Models;

import java.sql.*;
import java.util.Vector;

import Beans.NgonNgu;
/**
 *
 * @author Venus
 */
public class TuyChon_Model {
    public Vector LayDSTC(int langid) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select distinct(MaTC), MaNN, TenTC from tuychon where MaNN = " + langid + " Order by MaTC desc ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.TuyChon tc = new Beans.TuyChon();
                tc.setMaTC(rss.getInt(1));
                tc.setMaNN(rss.getInt(2));
                tc.setTenTC(rss.getString(3));
                v.add(tc);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }
    public static boolean KiemTraTenTC(String tentc) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select * from tuychon where TenTC = '" + tentc + "'";
            rss = stmt.executeQuery(query);
            boolean n = false;
            if (rss.next()) {
                n = true;
            }
            stmt.close();
            rss.close();
            conn.close();
            return n;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }
    public static int TaoMa() {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select max(MaTC) from tuychon";
            rss = stmt.executeQuery(query);
            int maht = 0;// biến maht chứa mã của khu vực hiện tại
            while (rss.next()) {
                maht = rss.getInt(1);
            }
            stmt.close();
            rss.close();
            conn.close();
            return maht + 1;
        } catch (Exception e) {
            e.printStackTrace();
            return 0;
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }
    public static boolean AddTC(int matc, String tentc)
    {
        Connection conn = null;

        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            NgonNgu[] dsnn = NgonNgu_Model.DSNgonNgu();
            String x = "insert into tuychon values ";
            for (int i = 0; i < dsnn.length; i++) {
                x = x + "(" + matc + ", " + dsnn[i].getMaNN() + ", '" + tentc + "'),";
            }
            String query = x.substring(0, x.length() - 1);
            int n = stmt.executeUpdate(query);
            stmt.close();
            conn.close();
            if (n == 0) {
                return false;// thất bại
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }            
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }
    public Vector Detail(int matc) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        Vector v = new Vector();
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select * from tuychon where MaTC = " + matc;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.TuyChon tc = new Beans.TuyChon();
                tc.setMaTC(rss.getInt(1));
                tc.setMaNN(rss.getInt(2));
                tc.setTenTC(rss.getString(3));
                v.add(tc);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }
    public static boolean KiemTraSuaTenTC(String tentc, String matc) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select * from tuychon where TenTC = '" + tentc + "' and MaTC != " + matc;
            rss = stmt.executeQuery(query);
            boolean n = false;
            if (rss.next()) {
                n = true;
            }
            stmt.close();
            rss.close();
            conn.close();
            return n;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }
    public static boolean Edit(String matc, String mann, String tentc) {
        Connection conn = null;

        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "Update tuychon set TenTC = '" + tentc + "' where MaTC = " + matc + " and MaNN = " + mann;
            int n = stmt.executeUpdate(query);
            stmt.close();
            conn.close();
            if (n == 0) {
                return false;
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }
    public Vector Search(String tentc, int langid) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        Vector v = new Vector();
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select distinct(MaTC), MaNN, TenTC from tuychon where MaNN = " + langid + " and TenTC like '%"+tentc+"%' Order by MaTC desc ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.TuyChon tc = new Beans.TuyChon();
                tc.setMaTC(rss.getInt(1));
                tc.setMaNN(rss.getInt(2));
                tc.setTenTC(rss.getString(3));
                v.add(tc);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }
    public Vector SortTenTC(int langid, String order) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        Vector v = new Vector();
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select distinct(MaTC), MaNN, TenTC from tuychon where MaNN = " + langid + " Order by TenTC " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.TuyChon tc = new Beans.TuyChon();
                tc.setMaTC(rss.getInt(1));
                tc.setMaNN(rss.getInt(2));
                tc.setTenTC(rss.getString(3));
                v.add(tc);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }
    public Vector SortSearchTenTC( int langid, String order, String tentc) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        Vector v = new Vector();
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select distinct(MaTC), MaNN, TenTC from tuychon where MaNN = " + langid + " and TenTC like '%"+tentc+"%' Order by TenTC "+ order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.TuyChon tc = new Beans.TuyChon();
                tc.setMaTC(rss.getInt(1));
                tc.setMaNN(rss.getInt(2));
                tc.setTenTC(rss.getString(3));
                v.add(tc);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }
    public static boolean CheckOption( String tentc, int langid) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            int n = 0;
            String query = "select * from tuychon where MaNN = "+langid+" and TenTC = '"+tentc+"' ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                n = n + 1;
            }
            stmt.close();
            rss.close();
            conn.close();
            if (n == 0) {
                return false;// thất bại
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }
    public static int LayMaTC(String TenTC, int langid) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        int maht = 0;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select MaTC from tuychon where  TenTC = '" + TenTC+ "' and MaNN = "+langid+" ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                maht = rss.getInt(1);
            }
            stmt.close();
            rss.close();
            conn.close();

        } catch (Exception e) {
            e.printStackTrace();
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return maht;
    }
    public static boolean CheckOptionFood( int matc, String mama) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
           ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            int n = 0;
            String query = "select * from tuychonmonan where MaMA = "+mama+" and MaTC = "+matc+" ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                n = n + 1;
            }
            stmt.close();
            rss.close();
            conn.close();
            if (n == 0) {
                return false;// thất bại
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }
    public static String LayTenTC(int matc, int langid) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        String maht = "";
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select TenTC from tuychon where  MaTC = " + matc+ " and MaNN = "+langid+" ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                maht = rss.getString(1);
            }
            stmt.close();
            rss.close();
            conn.close();

        } catch (Exception e) {
            e.printStackTrace();
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return maht;
    }
    public static String LayTenTCPDMT(String x, int langid) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        String maht = "";        
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select TenTC from tuychon where  MaTC in ("+x+") and MaNN = "+langid+" ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                maht = maht + rss.getString(1) + " , ";
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
       finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return maht;
    }
    public static Vector LoadTuyChon( int langid,String ma) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select TC.MaTC,MaNN,TenTC from tuychon TC, tuychonmonan TM where MaNN = " + langid + " and TC.MaTC = TM.MaTC and MaMA = " + ma;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.TuyChon tc = new Beans.TuyChon();
                tc.setMaTC(rss.getInt(1));
                tc.setMaNN(rss.getInt(2));
                tc.setTenTC(rss.getString(3));
                v.add(tc);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }
     public static boolean CheckTuyChon( String matc) {
         Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            int n = 0;
            String query = "select * from tuychon where MaTC = "+matc+" ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                n = n + 1;
            }
            stmt.close();
            rss.close();
            conn.close();
            if (n == 0) {
                return false;// thất bại
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }
}
